From 1cc9e694a1f6a53c396d9074f77d5d279018721b Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Tue, 14 Jul 2026 22:55:09 +0000 Subject: [PATCH] Redesign WATCHES.Type to be A-Rec, B-Rec, Location, and Other Instead of having a different WATCHES.Type for each behavior that does not have a corresponding follow record, and different types for feeding station arrivals and feeding station groom scans, have only 2 types. One for A records and one for B records. There are also types for locations and for "other", the latter of which is equalivant to a B-Record. Presumeably "other" is used for grad-student data, etc. --- conversion/load_aggressions.m4 | 23 +- conversion/load_follow_arrival.m4 | 4 +- conversion/load_follow_to_watches.sql | 2 +- db/schemas/lib/triggers/create/events.m4 | 188 +++----- .../lib/triggers/create/follow_observers.m4 | 7 +- .../lib/triggers/create/pantgrunts_view.m4 | 20 +- db/schemas/lib/triggers/create/roles.m4 | 6 +- db/schemas/lib/triggers/create/watches.m4 | 72 ++- db/schemas/lib/triggers/drop/events.m4 | 1 + db/schemas/sokwedb/indexes/create/watches.m4 | 39 +- db/schemas/sokwedb/indexes/drop/watches.m4 | 17 +- db/schemas/sokwedb/tables/create/watches.m4 | 10 +- doc/src/epilog.inc.m4 | 23 + doc/src/tables/events.m4 | 257 +++++------ doc/src/tables/follow_observers.m4 | 4 +- doc/src/tables/watches.m4 | 409 +++++++----------- include/global_constants.m4 | 7 +- 17 files changed, 476 insertions(+), 613 deletions(-) diff --git a/conversion/load_aggressions.m4 b/conversion/load_aggressions.m4 index 98e9720..1a89ab7 100644 --- a/conversion/load_aggressions.m4 +++ b/conversion/load_aggressions.m4 @@ -44,10 +44,29 @@ BEGIN LOOP -- Which follow belongs to the aggression_event? SELECT watches.wid - INTO STRICT related_wid + INTO related_wid FROM watches WHERE watches.animid = this_ae.ae_fol_b_focal_id - AND watches.date = this_ae.ae_date; + AND watches.date = this_ae.ae_date + -- Everything loaded is 'B', so far, but why depend on that? + AND watches.type = 'B'; + + IF NOT FOUND THEN + -- Make a new row in WATCHES + -- Problem #70 + INSERT INTO watches ( + animid + , commid + , date + , type + , notes) + VALUES (this_ae.ae_fol_b_focal_id + , this_ae.ae_cl_community_id + , this_ae.ae_date + , 'B' + , ''); + related_wid := CURRVAL('watches_wid_seq'); + END IF; -- aggression_event -> EVENTS INSERT INTO events ( diff --git a/conversion/load_follow_arrival.m4 b/conversion/load_follow_arrival.m4 index d60ebbe..f69dbd4 100644 --- a/conversion/load_follow_arrival.m4 +++ b/conversion/load_follow_arrival.m4 @@ -58,7 +58,9 @@ BEGIN INTO STRICT related_wid FROM watches WHERE watches.animid = this_fa.fa_fol_b_focal_animid - AND watches.date = this_fa.fa_fol_date; + AND watches.date = this_fa.fa_fol_date + -- Everything loaded is 'B', so far, but why depend on that? + AND watches.type = 'B'; -- follow_arrival -> EVENTS INSERT INTO events ( diff --git a/conversion/load_follow_to_watches.sql b/conversion/load_follow_to_watches.sql index 89c12ba..5269d34 100644 --- a/conversion/load_follow_to_watches.sql +++ b/conversion/load_follow_to_watches.sql @@ -63,7 +63,7 @@ INSERT INTO watches ( SELECT fol_b_animid , fol_cl_community_id , fol_date - , 'F' + , 'B' , '' FROM clean.follow ORDER BY fol_b_animid, fol_date; diff --git a/db/schemas/lib/triggers/create/events.m4 b/db/schemas/lib/triggers/create/events.m4 index c7c3d96..6161abd 100644 --- a/db/schemas/lib/triggers/create/events.m4 +++ b/db/schemas/lib/triggers/create/events.m4 @@ -76,132 +76,70 @@ CREATE OR REPLACE FUNCTION events_func () BEGIN -- The event's behavior must match the watch type - -- (follow, attendance, etc.). - - IF NEW.behavior IN ('sdb_aggression' - ,'sdb_arrival' - ,'sdb_food' - ,'sdb_grooming' - ,'sdb_groom_scan' - ,'sdb_other_species' - ,'sdb_colobus') THEN - -- The event must be related to a follow. - watch_type := 'sdb_follow'; - ELSIF NEW.behavior IN ('sdb_arrival_a' - ,'sdb_groom_scan_a') THEN - -- The event must be related to a feeding station attendance record. - watch_type := 'sdb_attendance'; - END IF; - - IF watch_type IS NOT NULL THEN - SELECT watches.animid, watches.commid, watches.date, watches.type - , watches.notes - INTO a_animid, a_commid , a_date , a_type - , a_notes - FROM watches - WHERE watches.wid = NEW.wid - AND watches.type <> watch_type; - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on ' || TG_OP || ' of EVENTS' - , DETAIL = 'Events with (Behavior) = (' - || NEW.behavior - || ') must be related to a WATCHES row with' - || ' (Type) = (' - || watch_type - || '): Key (EID) = (' - || NEW.eid - || '): Value (WID) = (' - || NEW.wid - || '): Value (Behavior) = (' - || NEW.behavior - || '), Value (Start) = (' - || NEW.start - || '), Value (Stop) = (' - || NEW.stop - || '), Value (Certainty) = (' - || NEW.certainty - || ') : Key (WATCHES.WID) = (' - || NEW.wid - || '), Value (WATCHES.AnimID) = (' - || a_animid - || '), Value (WATCHES.Date) = (' - || a_date - || '), Value (WATCHES.Type) = (' - || a_type - || '), Value (WATCHES.CommID) = (' - || a_commid - || '), Value (WATCHES.Notes) = (' - || a_notes - || ')'; - END IF; + -- (A-Record, B-Record, etc.). + -- If the watch type can be B-Record, it can always be Other also. + SELECT watches.animid, watches.commid, watches.date, watches.type + , watches.notes + INTO a_animid, a_commid , a_date , a_type + , a_notes + FROM watches + WHERE watches.wid = NEW.wid; + + IF (a_type = 'sdb_arec' + AND NEW.behavior <> 'sdb_arrival_a' + AND NEW.behavior <> 'sdb_aggression' + AND NEW.behavior <> 'sdb_groom_scan' + AND NEW.behavior <> 'sdb_grooming' + AND NEW.behavior <> 'sdb_mating_event' + AND NEW.behavior <> 'sdb_pg_event') + OR ((a_type = 'sdb_brec' + OR a_type = 'sdb_other_watch') + AND NEW.behavior <> 'sdb_aggression' + AND NEW.behavior <> 'sdb_arrival' + AND NEW.behavior <> 'sdb_brec_note' + AND NEW.behavior <> 'sdb_colobus' + AND NEW.behavior <> 'sdb_food' + AND NEW.behavior <> 'sdb_grooming' + AND NEW.behavior <> 'sdb_groom_scan' + AND NEW.behavior <> 'sdb_mating_event' + AND NEW.behavior <> 'sdb_other_species' + AND NEW.behavior <> 'sdb_pg_event') + OR (a_type = 'sdb_location' + AND NEW.behavior <> 'sdb_utm' + AND NEW.behavior <> 'sdb_paper') THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of EVENTS' + , DETAIL = 'Events with (Behavior) = (' + || NEW.behavior + || ') cannot be related to a WATCHES row with' + || ' (Type) = (' + || a_type + || '): Key (EID) = (' + || NEW.eid + || '): Value (WID) = (' + || NEW.wid + || '): Value (Behavior) = (' + || NEW.behavior + || '), Value (Start) = (' + || NEW.start + || '), Value (Stop) = (' + || NEW.stop + || '), Value (Certainty) = (' + || NEW.certainty + || ') : Key (WATCHES.WID) = (' + || NEW.wid + || '), Value (WATCHES.AnimID) = (' + || a_animid + || '), Value (WATCHES.Date) = (' + || a_date + || '), Value (WATCHES.Type) = (' + || a_type + || '), Value (WATCHES.CommID) = (' + || a_commid + || '), Value (WATCHES.Notes) = (' + || a_notes + || ')'; END IF; - - IF NEW.behavior = 'sdb_utm' - OR NEW.behavior = 'sdb_paper' - OR NEW.behavior = 'sdb_pg_event' - OR NEW.behavior = 'sdb_brec_note' THEN - -- The WATCHES row must be for a follow, if there is one, and if not - -- then for a location, if it's gps or a map location, - -- for a pantgrunt, if it's a pantgrunt, and then for a - -- B record, if it's a B record note. - -- There can't exist a WATCHES row for a follow and a location, - -- or for a follow and a pantgrunt, or for a follow and a B record - -- watch, for the same individual for the same date, so this - -- simplifies things. - - IF NEW.behavior = 'sdb_pg_event' THEN - watch_type := 'sdb_pantgrunt'; - ELSIF NEW.behavior = 'sdb_brec_note' THEN - watch_type := 'sdb_brec'; - ELSE - watch_type := 'sdb_location'; - END IF; - - SELECT watches.animid, watches.commid, watches.date, watches.type - , watches.notes - INTO a_animid, a_commid , a_date , a_type - , a_notes - FROM watches - WHERE watches.wid = NEW.wid - AND watches.type <> 'sdb_follow' - AND watches.type <> watch_type; - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on ' || TG_OP || ' of EVENTS' - , DETAIL = 'Events with (Behavior) = (' - || NEW.behavior - || ') must be related to a WATCHES row with' - || ' (Type) = (sdb_follow) or (Type) = (' - || watch_type - || '), but this is not the case: Key (EID) = (' - || NEW.eid - || '): Value (WID) = (' - || NEW.wid - || '): Value (Behavior) = (' - || NEW.behavior - || '), Value (Start) = (' - || NEW.start - || '), Value (Stop) = (' - || NEW.stop - || '), Value (Certainty) = (' - || NEW.certainty - || ') : Key (WATCHES.WID) = (' - || NEW.wid - || '), Value (WATCHES.AnimID) = (' - || a_animid - || '), Value (WATCHES.Date) = (' - || a_date - || '), Value (WATCHES.Type) = (' - || a_type - || '), Value (WATCHES.CommID) = (' - || a_commid - || '), Value (WATCHES.Notes) = (' - || a_notes - || ')'; - END IF; - END IF; - END; END IF; diff --git a/db/schemas/lib/triggers/create/follow_observers.m4 b/db/schemas/lib/triggers/create/follow_observers.m4 index b0d0157..d789b4c 100644 --- a/db/schemas/lib/triggers/create/follow_observers.m4 +++ b/db/schemas/lib/triggers/create/follow_observers.m4 @@ -53,12 +53,15 @@ CREATE OR REPLACE FUNCTION follow_observers_func () , a_notes FROM watches WHERE watches.wid = NEW.wid - AND watches.type <> 'sdb_follow'; + AND watches.type <> 'sdb_brec' + AND watches.type <> 'sdb_other_watch'; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on ' || TG_OP || ' of FOLLOW_OBSERVERS' , DETAIL = 'Observers must be related to a WATCHES row with' - || ' (Type) = (sdb_follow)' + || ' (Type) = (' + || a_type + || ')' ||': Key (FOID) = (' || NEW.FOID || '): Value (WID) = (' diff --git a/db/schemas/lib/triggers/create/pantgrunts_view.m4 b/db/schemas/lib/triggers/create/pantgrunts_view.m4 index a05128b..8982f65 100644 --- a/db/schemas/lib/triggers/create/pantgrunts_view.m4 +++ b/db/schemas/lib/triggers/create/pantgrunts_view.m4 @@ -118,7 +118,7 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () -- that's consistent with the way DYADS searches. -- -- In this particular case the Start and Stop values are guarenteed - -- to be identical, and the default watch type is 'sdb_pantgrunt'. + -- to be identical, and the default watch type is 'sdb_brec'. -- And Certainty is guarenteed to be sdb_identity_certain. So this -- code is aware of that and does not even mention Certainty to the -- user. @@ -239,13 +239,15 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () FROM watches WHERE watches.animid = NEW.animid AND watches.date = NEW.date - AND (watches.type = 'sdb_follow' - OR watches.type = 'sdb_pantgrunt'); + AND (watches.type = 'sdb_brec' + OR watches.type = 'sdb_other_watch') + -- Use sdb_brec as the default when there's both. + ORDER BY watches.type = 'sdb_brec' DESC; IF NOT FOUND THEN -- When a WATCHES row must be created then there must - -- not be an existing follow, so use the pantgrunt Type. - target_type := 'sdb_pantgrunt'; + -- not be an existing follow, so use the sdb_other_watch Type. + target_type := 'sdb_other_watch'; END IF; ELSE -- Use the user's chosen type and let the errors fly as they may. @@ -330,8 +332,8 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () IF NOT FOUND THEN -- When a WATCHES row must be created then there must - -- not be an existing follow, so use the pantgrunt Type. - target_type := 'sdb_pantgrunt'; + -- not be an existing follow, so use the sdb_other_watch Type. + target_type := 'sdb_other_watch'; END IF; ELSE -- Use the user's chosen type and let the errors fly as they may. @@ -363,8 +365,8 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () OR (NEW.animid IS NOT NULL AND NEW.animid <> target_animid) OR (NEW.type IS NOT NULL - AND NEW.type <> 'sdb_follow' - AND NEW.type <> 'sdb_pantgrunt') + AND NEW.type <> 'sdb_brec' + AND NEW.type <> 'sdb_other_watch') OR (NEW.commid IS NOT NULL AND NEW.commid <> target_commid) OR (NEW.time IS NOT NULL diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 2afbace..e3393f9 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -306,7 +306,7 @@ CREATE OR REPLACE FUNCTION roles_func () OR a_behavior = 'sdb_groom_scan' OR a_behavior = 'sdb_groom_scan_a' OR a_behavior = 'sdb_pg_event' - OR a_behavior = 'sdb_mating') THEN + OR a_behavior = 'sdb_mating_event') THEN DECLARE a_pid roles.pid%TYPE; a_role roles.role%TYPE; @@ -328,7 +328,7 @@ CREATE OR REPLACE FUNCTION roles_func () AND NEW.role <> 'sdb_actee' AND ((a_behavior = 'sdb_aggression' OR a_behavior = 'sdb_groom_scan' - OR a_behavior = 'sdb_mating') + OR a_behavior = 'sdb_mating_event') OR (NEW.role <> 'sdb_mutual' AND (a_behavior = 'sdb_groom_scan_a' OR a_behavior = 'sdb_pg_event' @@ -373,7 +373,7 @@ CREATE OR REPLACE FUNCTION roles_func () IF a_behavior = 'sdb_aggression' OR a_behavior = 'sdb_groom_scan' - OR a_behavior = 'sdb_mating' THEN + OR a_behavior = 'sdb_mating_event' THEN -- Role is sdb_actor or sdb_actee. There can be only one row -- with each of these roles per aggression/grooming/mating -- event. diff --git a/db/schemas/lib/triggers/create/watches.m4 b/db/schemas/lib/triggers/create/watches.m4 index 97ace51..ca1d269 100644 --- a/db/schemas/lib/triggers/create/watches.m4 +++ b/db/schemas/lib/triggers/create/watches.m4 @@ -530,7 +530,8 @@ CREATE OR REPLACE FUNCTION watches_func () IF TG_OP = 'UPDATE' AND NEW.animid <> OLD.animid THEN - -- The individual eating must be the focal. + -- The individual eating (B-Rec) or arriving at the feeding station + -- (A-Rec), or being located, must be the "focal", the AnimID. SELECT events.eid, events.behavior, events.start, events.stop , roles.pid, roles.role, roles.participant INTO a_eid , a_behavior , a_start , a_stop @@ -539,12 +540,24 @@ CREATE OR REPLACE FUNCTION watches_func () JOIN roles ON (roles.eid = events.eid) WHERE events.wid = NEW.wid - AND events.behavior = 'sdb_food' + AND (events.behavior = 'sdb_food' + OR events.behavior = 'sdb_utm' + OR events.behavior = 'sdb_paper' + OR events.behavior = 'sdb_arrival_a') AND roles.participant <> NEW.animid; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on UPDATE of WATCHES' - , DETAIL = 'The individual eating must the the focal' + , DETAIL = 'The individual ' + || CASE + WHEN a_behavior = 'sdb_food' THEN + 'eating' + WHEN a_behavior = 'sdb_arrival_a' THEN + 'arriving at the feeding station' + ELSE -- a_behavior = 'sdb_utm' or 'sdb_paper' + 'located' + END + || ' must the the observed individual, the WATCHES.AnimID' || ': Key (WID) = (' || NEW.wid || '), Value (AnimID) = (' @@ -587,17 +600,37 @@ CREATE OR REPLACE FUNCTION watches_insert_commit_func () -- -- Validate follows -- - IF NEW.Type = 'sdb_follow' THEN + IF NEW.type = 'sdb_brec' + OR NEW.type = 'sdb_other_watch' THEN - -- A row must exist on FOLLOW_OBSERVERS - PERFORM 1 - FROM follow_observers - WHERE follow_observers.wid = NEW.wid; - IF NOT FOUND THEN + -- A row must exist on FOLLOW_OBSERVERS when there are arrivals + DECLARE + a_eid events.eid%TYPE; + a_start events.start%TYPE; + a_stop events.stop%TYPE; + a_certainty events.certainty%TYPE; + a_notes events.notes%TYPE; + + BEGIN + SELECT events.eid + , events.start, events.stop, events.certainty, events.notes + INTO a_eid + , a_start , a_stop , a_certainty , a_notes + FROM events + WHERE events.wid = NEW.wid + AND events.behavior = 'sdb_arrival' + -- Reproducable error + ORDER BY events.eid; + + IF FOUND + AND NOT EXISTS + (SELECT 1 + FROM follow_observers + WHERE follow_observers.wid = NEW.wid) THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on INSERT of WATCHES' - , DETAIL = 'A row on WATCHES must have a related row on' - || ' FOLLOW_OBSERVERS' + , DETAIL = 'A row on WATCHES that has related arrivals' + || ' must have a related row on FOLLOW_OBSERVERS' || ': Key (WID) = (' || NEW.wid || '), Value (AnimID) = (' @@ -606,8 +639,23 @@ CREATE OR REPLACE FUNCTION watches_insert_commit_func () || NEW.Date || '), Value (CommID) = (' || NEW.commid + || ': Key (EVENTS.EID) = (' + || a_eid + || '), Value (EVENTS.WID) = (' + || NEW.wid + || '), Value (EVENTS.Behavior) = (' + || 'sdb_arrival' + || '), Value (EVENTS.Start) = (' + || a_start + || '), Value (EVENTS.Stop) = (' + || a_stop + || '), Value (EVENTS.certainty) = (' + || a_certainty + || '), Value (EVENTS.Notes) = (' + || a_Notes || ')'; END IF; + END; IF TG_OP = 'UPDATE' AND NEW.animid <> OLD.animid THEN @@ -637,7 +685,7 @@ CREATE OR REPLACE FUNCTION watches_insert_commit_func () IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on UPDATE of WATCHES' - , DETAIL = 'One of the individuals involved in the grooming event' + , DETAIL = 'One of the individuals involved in the grooming' || ' event must be the focal, who must be either the' || ' individual doing the grooming or the individual' || ' being groomed' diff --git a/db/schemas/lib/triggers/drop/events.m4 b/db/schemas/lib/triggers/drop/events.m4 index 1ea239e..a54c598 100644 --- a/db/schemas/lib/triggers/drop/events.m4 +++ b/db/schemas/lib/triggers/drop/events.m4 @@ -22,4 +22,5 @@ include(`copyright.m4')dnl DROP FUNCTION IF EXISTS events_func() CASCADE; DROP FUNCTION IF EXISTS events_update_func() CASCADE; +DROP FUNCTION IF EXISTS events_insert_commit_func() CASCADE; DROP FUNCTION IF EXISTS events_delete_commit_func() CASCADE; diff --git a/db/schemas/sokwedb/indexes/create/watches.m4 b/db/schemas/sokwedb/indexes/create/watches.m4 index f2b4640..8e9f10d 100644 --- a/db/schemas/sokwedb/indexes/create/watches.m4 +++ b/db/schemas/sokwedb/indexes/create/watches.m4 @@ -22,44 +22,9 @@ include(`constants.m4')dnl include(`indexmacros.m4')dnl CREATE UNIQUE INDEX IF NOT EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_brec) means Date + AnimID must be unique" + "On WATCHES, Type+Date+AnimID must be unique" ON watches - (date, animid) - WHERE type = 'sdb_follow' - OR type = 'sdb_brec'; - -CREATE UNIQUE INDEX IF NOT EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_location) means Date + AnimID must be unique" - ON watches - (date, animid) - WHERE type = 'sdb_follow' - OR type = 'sdb_location'; - -CREATE UNIQUE INDEX IF NOT EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_mating) means Date + AnimID must be unique" - ON watches - (date, animid) - WHERE type = 'sdb_follow' - OR type = 'sdb_mating'; - -CREATE UNIQUE INDEX IF NOT EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_pantgrunt) means Date + AnimID must be unique" - ON watches - (date, animid) - WHERE type = 'sdb_follow' - OR type = 'sdb_pantgrunt'; - -CREATE UNIQUE INDEX IF NOT EXISTS - "On WATCHES where (Type)=(sdb_attendance), Date + AnimID must be unique" - ON watches - (date, animid) - WHERE type = 'sdb_attendance'; - -CREATE UNIQUE INDEX IF NOT EXISTS - "On WATCHES where (Type)=(sdb_ag_scan), Date must be unique" - ON watches - (date) - WHERE type = 'sdb_ag_scan'; + (type, date, animid); CREATE INDEX IF NOT EXISTS watches_date_animid_type ON watches (date, animid, type); diff --git a/db/schemas/sokwedb/indexes/drop/watches.m4 b/db/schemas/sokwedb/indexes/drop/watches.m4 index 684c2fe..ffdfde4 100644 --- a/db/schemas/sokwedb/indexes/drop/watches.m4 +++ b/db/schemas/sokwedb/indexes/drop/watches.m4 @@ -22,22 +22,7 @@ include(`constants.m4')dnl include(`indexmacros.m4')dnl DROP INDEX IF EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_brec) means Date + AnimID must be unique"; - -DROP INDEX IF EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_location) means Date + AnimID must be unique"; - -DROP INDEX IF EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_mating) means Date + AnimID must be unique"; - -DROP INDEX IF EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_pantgrunt) means Date + AnimID must be unique"; - -DROP INDEX IF EXISTS - "On WATCHES where (Type)=(sdb_attendance), Date + AnimID must be unique"; - -DROP INDEX IF EXISTS - "On WATCHES where (Type)=(sdb_ag_scan), Date must be unique"; + "On WATCHES, Type+Date+AnimID must be unique"; DROP INDEX IF EXISTS watches_date_animid_type; DROP INDEX IF EXISTS watches_animid; diff --git a/db/schemas/sokwedb/tables/create/watches.m4 b/db/schemas/sokwedb/tables/create/watches.m4 index 679a77f..e8a3761 100644 --- a/db/schemas/sokwedb/tables/create/watches.m4 +++ b/db/schemas/sokwedb/tables/create/watches.m4 @@ -49,13 +49,11 @@ CREATE TABLE watches ( CONSTRAINT "The WATCHES.Date must not be before sdb_min_follow_date" CHECK(date >= 'sdb_min_follow_date') ,type TEXT NOT NULL - CONSTRAINT "Watches.Type must be one of: sdb_attendance, sdb_follow, sdb_ag_scan, sdb_location, sdb_pantgrunt" - CHECK (type = 'sdb_attendance' - OR type = 'sdb_follow' - OR type = 'sdb_ag_scan' + CONSTRAINT "Watches.Type must be one of: sdb_arec, sdb_brec, sdb_location, sdb_other_watch" + CHECK (type = 'sdb_arec' + OR type = 'sdb_brec' OR type = 'sdb_location' - OR type = 'sdb_mating' - OR type = 'sdb_pantgrunt') + OR type = 'sdb_other_watch') ,notes TEXT NOT NULL notonlyspaces_check(`Notes') ); diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index fe0925c..45e3985 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -864,5 +864,28 @@ sdb_generated_rst()dnl .. |spx_dad_prelim| replace:: ``sdb_dad_prelim`` +.. EVENTS.Behavior values +.. |spx_arrival_a| replace:: ``sdb_arrival_a`` +.. |spx_aggression| replace:: ``sdb_aggression`` +.. |spx_groom_scan_a| replace:: ``sdb_groom_scan_a`` +.. |spx_arrival| replace:: ``sdb_arrival`` +.. |spx_brec_note| replace:: ``sdb_brec_note`` +.. |spx_colobus| replace:: ``sdb_colobus`` +.. |spx_food| replace:: ``sdb_food`` +.. |spx_utm| replace:: ``sdb_utm`` +.. |spx_groom_scan| replace:: ``sdb_groom_scan`` +.. |spx_grooming| replace:: ``sdb_grooming`` +.. |spx_paper| replace:: ``sdb_paper`` +.. |spx_mating_event| replace:: ``sdb_mating_event`` +.. |spx_other_species| replace:: ``sdb_other_species`` +.. |spx_pg_event| replace:: ``sdb_pg_event`` + + +.. WATCHES.Type values +.. |spx_arec| replace:: ``sdb_arec`` +.. |spx_brec| replace:: ``sdb_brec`` +.. |spx_location| replace:: ``sdb_location`` +.. |spx_other_watch| replace:: ``sdb_other_watch`` + sdb_if_htmlout(*m4[Page last generated: |today|]m4*, *m4[]m4*) diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index 3a6caf3..eb8df16 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -19,6 +19,36 @@ include(macros.m4)dnl sdb_rst_quotes(`on')dnl sdb_generated_rst()dnl +.. Make substitution references for common textual blurbs + +.. |_b_record_protocol| replace:: + The EVENTS row must be associated with an observation that has a + B-Record-like protocol. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of either ``sdb_brec`` or + ``sdb_other_watch``. + +.. |_a_record_protocol| replace:: + The EVENTS row must be associated with an observation that has a + A-Record-like protocol. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_arec``. + +.. |_ab_record_protocol| replace:: + The EVENTS row must be associated with an observation that is not a + location. + The related |WATCHES| row can be of an A-Record, a B-Record, or a + Other |WATCHES|.\ |WATCHES.Type|. + This means the |EVENTS.WID| column must not reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_location``. + +.. |_location_protocol| replace:: + The EVENTS row must be associated with a |WATCHES| row that records + a location. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_location``. + + .. _EVENTS: EVENTS @@ -61,10 +91,7 @@ The following table lists these rules and implications: .. _EVENTS_arrival_a_code: ``sdb_arrival_a`` (Arrival at the feeding station) - The EVENTS row must be associated with a record of attendance at - the feeding station. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_attendance``. + |_a_record_protocol| A related row should exist on |ARRIVALS_A|; there should be a row on |ARRIVALS_A| with an |ARRIVALS_A|.\ |ARRIVALS_A.EID| value of @@ -111,9 +138,7 @@ The following table lists these rules and implications: .. _EVENTS_aggression_code: ``sdb_aggression`` (Aggression) - The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. + |_ab_record_protocol| A related row should exist on |AGGRESSIONS|; there should be a row on |AGGRESSIONS| with an |AGGRESSIONS|.\ |AGGRESSIONS.EID| value of @@ -157,10 +182,7 @@ The following table lists these rules and implications: .. _EVENTS_groom_scan_a_code: ``sdb_groom_scan_a`` (Attendance SCAN interval Groomings) - The EVENTS row must be associated with a interval scan for grooming - done at the feeding station. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_ag_scan``. + |_a_record_protocol| The |ROLES| rows related to the event, the rows with a |ROLES|.\ |ROLES.EID| value equal to the EVENTS.\ |EVENTS.EID| value, @@ -195,9 +217,7 @@ The following table lists these rules and implications: .. _EVENTS_arrival_code: ``sdb_arrival`` (Arrival) - The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. + |_b_record_protocol| A related row should exist on |ARRIVALS|; there should be a row on |ARRIVALS| with an |ARRIVALS|.\ |ARRIVALS.EID| value of the event's @@ -209,7 +229,8 @@ The following table lists these rules and implications: The |ROLES| row related to the event, the row with a |ROLES|.\ |ROLES.EID| value equal to the EVENTS.\ |EVENTS.EID| value, designates the arriving/departing individual. - There must be at most one |ROLES| row related to the arrival event. + There must be exactly one |ROLES| row related to the arrival event, + but only when there is a related |ARRIVALS| row. The EVENTS.\ |EVENTS.Start| column contains the arrival time of the individual identified in the related |ROLES| row. @@ -221,14 +242,7 @@ The following table lists these rules and implications: .. _EVENTS_brec_note_code: ``sdb_brec_note`` (B-RECord note translation) - The EVENTS row must be associated with a |WATCHES| row representing - either a follow or a B-Record taking. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with a |WATCHES|.\ |WATCHES.Type| value of either ``sdb_follow`` or - ``sdb_brec``. - Further, as explained in the |WATCHES| documentation, if there is a - follow for a given individual on a given day then the EVENTS row - must be associated with the follow. + |_b_record_protocol| A related row should exist on |BRECORD_NOTES|; there should be a row on |BRECORD_NOTES| with a |BRECORD_NOTES|.\ @@ -273,9 +287,7 @@ The following table lists these rules and implications: There is one row for each record of colobus monkey/chimpanzee interaction. - The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. + |_b_record_protocol| A related row should exist on |COLOBUS|; there should be a row on |COLOBUS| with an |COLOBUS|.\ |COLOBUS.EID| value of the @@ -303,9 +315,7 @@ The following table lists these rules and implications: .. _EVENTS_food_code: ``sdb_food`` (Food) - The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. + |_b_record_protocol| A related row should exist on |FOOD_EVENTS|; there should be a row on |FOOD_EVENTS| with a |FOOD_EVENTS|.\ |FOOD_EVENTS.EID| value of @@ -346,15 +356,8 @@ The following table lists these rules and implications: This means that the combination of |EVENTS.WID| and |EVENTS.Start| must be unique. - The EVENTS row must be associated with a |WATCHES| row representing - either a follow or a location. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with a |WATCHES|.\ |WATCHES.Type| value of either ``sdb_follow`` or - ``sdb_location``. - Further, as explained in the |WATCHES| documentation, if there is a - follow for a given individual on a given day then the EVENTS row - must be associated with the follow. - + |_location_protocol| + A related row should exist on |LOCATIONS_UTM|; there should be a row on |LOCATIONS_UTM| with a |LOCATIONS_UTM|.\ |LOCATIONS_UTM.EID| value of the event's |EVENTS.EID|. @@ -370,10 +373,8 @@ The following table lists these rules and implications: The system will generate a warning when there is no |ROLES| row related to the UTM location event. - The individual located must be the the focal of the follow, when - the related |WATCHES| row represents a follow, and must be located - individual when the related |WATCHES| row records the date of a - spatial location. + The individual located must be the individual the related |WATCHES| + row records as the located individual. This means that the |ROLES| row related to the event must have a |ROLES|.\ |ROLES.Participant| value equal to the |WATCHES|.\ |WATCHES.AnimID| of the |WATCHES| row related to the UTM location @@ -385,20 +386,22 @@ The following table lists these rules and implications: This means the value of the EVENTS.\ |EVENTS.Start| column must equal the value of the EVENTS.\ |EVENTS.Stop| column. - When the located individual is the focal of a follow -- when the - related |WATCHES|.\ |WATCHES.Type| value is ``sdb_follow`` -- the - time of the spatial recording should occur when the focal was under - observation. + The time of the spatial recording is expected to correspond with a + time when a focal of a follow was under observation. The system will generate a warning when there is not an |ARRIVALS| - related row that records the presence of the focal at the time the + related row that records the presence of a focal at the time the spatial observation occurred. This means that a warning will be generated when there is no - related EVENTS row where the |EVENTS.Behavior| code is - ``sdb_arrival`` and the EVENTS.\ |EVENTS.Start| value is less - than or equal to the time of the GPS location event value and the - EVENTS.\ |EVENTS.Stop| value is greater than or equal to the time - of the location event, and the event has a related |ROLES| row - where the |ROLES|.\ |ROLES.Participant| is the located individual. + |WATCHES| row with a |WATCHES|.\ |WATCHES.Type| of ``sdb_brec``, a + |WATCHES|.\ |WATCHES.Date| and a |WATCHES|.\ |WATCHES.AnimID| that + match the date and individual of the location |WATCHES| row related + to the UTM event, and that |WATCHES| row does not have a related + EVENTS row where the |EVENTS.Behavior| code is ``sdb_arrival`` and + the EVENTS.\ |EVENTS.Start| value is less than or equal to the time + of the UTM location event value and the EVENTS.\ |EVENTS.Stop| + value is greater than or equal to the time of the location event, + and the event has a related |ROLES| row where the |ROLES|.\ + |ROLES.Participant| is the located individual. For UTM location events, the EVENTS.\ |EVENTS.Certainty| column must be ``sdb_identity_certain``. @@ -407,9 +410,7 @@ The following table lists these rules and implications: .. _EVENTS_grooming_code: ``sdb_grooming`` (Grooming) - The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. + |_ab_record_protocol| A related row should exist on |GROOMINGS|; there should be a row on |GROOMINGS| with a |GROOMINGS|.\ |GROOMINGS.EID| value of the @@ -438,11 +439,11 @@ The following table lists these rules and implications: differ. One of the individuals in a grooming event must be the focal - individual. + individual, for B-Records, or must be the observed individual, for + A-Records. This means that on of the the |ROLES| rows related to the event must have a |ROLES.Participant| value equal to the |WATCHES|.\ - |WATCHES.AnimID| of the follow that is related to the grooming - event. + |WATCHES.AnimID| that is related to the grooming event. For grooming events, the EVENTS.\ |EVENTS.Certainty| column records the certainty of the duration of the grooming event. @@ -454,9 +455,7 @@ The following table lists these rules and implications: .. _EVENTS_groom_scan_code: ``sdb_groom_scan`` (SCAN interval Groomings) - The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. + |_b_record_protocol| A related row should exist on |GROOM_SCANS_B|; there should be a row on |GROOM_SCANS_B| with an |GROOM_SCANS_B|.\ |GROOM_SCANS_B.EID| value of @@ -496,7 +495,7 @@ The following table lists these rules and implications: |EVENTS.Certainty| column must be ``sdb_identity_certain``. -.. _EVENTS_map_code: +.. _EVENTS_paper_code: ``sdb_paper`` (PAPER map location) A record of an individual's location recorded using map @@ -507,14 +506,7 @@ The following table lists these rules and implications: This means that the combination of |EVENTS.WID| and |EVENTS.Start| must be unique. - The EVENTS row must be associated with a |WATCHES| row representing - either a follow or a location. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with a |WATCHES|.\ |WATCHES.Type| value of either ``sdb_follow`` or - ``sdb_location``. - Further, as explained in the |WATCHES| documentation, if there is a - follow for a given individual on a given day then the EVENTS row - must be associated with the follow. + |_location_protocol| A related row should exist on |LOCATIONS_PAPER|; there should be a row on |LOCATIONS_PAPER| with a |LOCATIONS_PAPER|.\ |LOCATIONS_PAPER.EID| @@ -531,10 +523,8 @@ The following table lists these rules and implications: The system will generate a warning when there is no |ROLES| row related to the map location event. - The individual located must be the the focal of the follow, when - the related |WATCHES| row represents a follow, and must be located - individual when the related |WATCHES| row records the date of a - spatial location. + The individual located must be the individual the related |WATCHES| + row records as the located individual. This means that the |ROLES| row related to the event must have a |ROLES|.\ |ROLES.Participant| value equal to the |WATCHES|.\ |WATCHES.AnimID| of the |WATCHES| row related to the map location @@ -546,20 +536,22 @@ The following table lists these rules and implications: This means the value of the EVENTS.\ |EVENTS.Start| column must equal the value of the EVENTS.\ |EVENTS.Stop| column. - When the located individual is the focal of a follow -- when the - related |WATCHES|.\ |WATCHES.Type| value is ``sdb_follow`` -- the - time of the spatial recording should occur when the focal was under - observation. + The time of the spatial recording expected to correspond with a + time when a focal of a follow was under observation. The system will generate a warning when there is not an |ARRIVALS| - related row that records the presence of the focal at the time the + related row that records the presence of a focal at the time the spatial observation occurred. This means that a warning will be generated when there is no - related EVENTS row where the |EVENTS.Behavior| code is - ``sdb_arrival`` and the EVENTS.\ |EVENTS.Start| value is less - than or equal to the time of the map location event value and the - EVENTS.\ |EVENTS.Stop| value is greater than or equal to the time - of the location event, and the event has a related |ROLES| row - where the |ROLES|.\ |ROLES.Participant| is the located individual. + |WATCHES| row with a |WATCHES|.\ |WATCHES.Type| of ``sdb_brec``, a + |WATCHES|.\ |WATCHES.Date| and a |WATCHES|.\ |WATCHES.AnimID| that + match the date and individual of the location |WATCHES| row related + to the spatial event, and that |WATCHES| row does not have a related + EVENTS row where the |EVENTS.Behavior| code is ``sdb_arrival`` and + the EVENTS.\ |EVENTS.Start| value is less than or equal to the time + of the spatial location event value and the EVENTS.\ |EVENTS.Stop| + value is greater than or equal to the time of the location event, + and the event has a related |ROLES| row where the |ROLES|.\ + |ROLES.Participant| is the located individual. For map location events, the EVENTS.\ |EVENTS.Certainty| column must be ``sdb_identity_certain``. @@ -568,11 +560,7 @@ The following table lists these rules and implications: .. _EVENTS_mating_event_code: ``sdb_mating_event`` (Mating) - The EVENTS row must be associated with either a follow or an - non-follow mating observervation. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with either a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow`` or - a |WATCHES|.\ |WATCHES.Type| value of ``sdb_mating``. + |_ab_record_protocol| A related row should exist on |MATINGS|; there should be a row on |MATINGS| with a |MATINGS|.\ |MATINGS.EID| value of the event's @@ -615,9 +603,7 @@ The following table lists these rules and implications: .. _EVENTS_other_species_code: ``sdb_other_species`` (Other Species) - The EVENTS row must be associated with a follow. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. + |_b_record_protocol| A related row should exist on either |SPECIES_PRESENT| or on |HUMANS|. @@ -639,11 +625,7 @@ The following table lists these rules and implications: .. _EVENTS_pantgrunt_code: ``sdb_pg_event`` (Pantgrunt) - The EVENTS row must be associated with either a follow or an - non-follow pantgrunt observervation. - This means the |EVENTS.WID| column must reference a |WATCHES| row - with either a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow`` or - a |WATCHES|.\ |WATCHES.Type| value of ``sdb_pantgrunt``. + |_ab_record_protocol| A related row should exist on |PANTGRUNTS|; there should be a row on |PANTGRUNTS| with a |PANTGRUNTS|.\ |PANTGRUNTS.EID| value of the @@ -727,46 +709,43 @@ information may, or must, be related to the event. In other words, what other tables may, or must, contain additional information about the event. -The following list summarizes the available codes: - -``sdb_arrival_a`` (Arrival at the feeding station) - A row must exist on |ARRIVALS_A| - -``sdb_aggression`` (Aggression) - A row must exist on |AGGRESSIONS|. - -``sdb_groom_scan_a`` (attendance SCAN interval Groomings) - There is no event-specific table containing further detail. - -``sdb_arrival`` (Arrival) - A row must exist on |ARRIVALS|. - -``sdb_brec_note`` (B-RECord note) - A row must exist on |BRECORD_NOTES|. - -``sdb_utm`` (UTM Location) - A row must exist on |LOCATIONS_UTM|. - -``sdb_food`` (Food) - A row must exist on |FOOD_EVENTS|. - -``sdb_grooming`` (Grooming) - A row must exist on |GROOMINGS|. - -``sdb_groom_scan`` (follow SCAN interval Groomings) - A row must exist on |GROOM_SCANS_B|. - -``sdb_mating_event`` (Mating) - A row must exist on |MATINGS|. - -``sdb_paper`` (PAPER map location) - A row must exist on |LOCATIONS_PAPER|. - -``sdb_other_species`` (Other Species) - A row must exist on |SPECIES_PRESENT| and/or on |HUMANS|. - -``sdb_pg_event`` (Pantgrunt) - A row must exist on |PANTGRUNTS|. +The following table summarizes the available codes: + ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| | | |WATCHES|.\ |WATCHES.Type|\ s allowed | | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |EVENTS.Behavior| | | | | | | | +| code | Description | |spx_arec| | |spx_brec| | |spx_other_watch| | |spx_location| | A row must exist on | ++====================+================================+============+============+===================+================+=====================+ +| |spx_arrival_a| | Arrival at the feeding station | Y | - | - | - | |ARRIVALS_A| | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |spx_aggression| | Aggression | Y | Y | Y | - | |AGGRESSIONS| | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |spx_groom_scan_a| | Feeding station groom scans | Y | - | - | - | None | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |spx_arrival| | Arrival | - | Y | Y | - | |ARRIVALS| | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |spx_brec_note| | B-Record translation notes | - | Y | Y | - | |BRECORD_NOTES| | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |spx_colobus| | Colobus encounter | - | Y | Y | - | |COLOBUS| | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |spx_utm| | UTM location | - | - | - | Y | |LOCATIONS_UTM| | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |spx_food| | Food bout | - | Y | Y | - | |FOOD_EVENTS| | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |spx_grooming| | Grooming | Y | Y | Y | - | |GROOMINGS| | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |spx_groom_scan| | Follow scans for groomings | - | Y | Y | - | |GROOM_SCANS_B| | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |spx_mating_event| | Mating | Y | Y | Y | - | |MATINGS| | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |spx_paper| | Paper map location | - | - | - | Y | |LOCATIONS_PAPER| | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |spx_other_species|| Other species | - | Y | Y | - | |SPECIES_PRESENT| | +| | | | | | | and/or |HUMANS| | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ +| |spx_pg_event| | Pantgrunt | Y | Y | Y | - | |PANTGRUNTS| | ++--------------------+--------------------------------+------------+------------+-------------------+----------------+---------------------+ See :ref:`above ` for further detail regarding what related data each code requires, or is expected to have. diff --git a/doc/src/tables/follow_observers.m4 b/doc/src/tables/follow_observers.m4 index a18159e..e9a5de5 100644 --- a/doc/src/tables/follow_observers.m4 +++ b/doc/src/tables/follow_observers.m4 @@ -35,9 +35,9 @@ column). There are no data validation rules that prevent the BRec observer from being the same observer as the Tiki observer. -The observers can only have observed a follow. +The observers can only have observed a B-Record type observation. This means that the |FOLLOW_OBSERVERS.WID| must be that of a |WATCHES| -row with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_follow``. +row with a |WATCHES|.\ |WATCHES.Type| value of ``sdb_brec``. More than two observers may be related to a follow by creating multiple FOLLOW_OBSERVERS rows that are related to a single row in the diff --git a/doc/src/tables/watches.m4 b/doc/src/tables/watches.m4 index fdd3db7..0cc3f4e 100644 --- a/doc/src/tables/watches.m4 +++ b/doc/src/tables/watches.m4 @@ -25,52 +25,77 @@ WATCHES ------- .. |WATCHES_summary| replace:: + WATCHES contains one row per date, per type of observation, per + individual. + The observation types are A-Record, B-Record, Location, and Other. + The individual that is associated with the entire observation + is dependent on the type of the observation. + B-Record type observations are often follows, but need not be. + B-Record type observations can consist solely of ad-hoc + observations that are normally recorded during a follow, such as a + pantgrunt, mating, aggression, etc. - WATCHES contains one row, per date, per individual, whenever there - was an intention to observe a given individual on a given date. - It also contains one row for every date there was an ad-hoc - observation *not* associated with any particular individual. - There is not a row for every day every individual was observed - because SokweDB contains data on individuals collected due to - proximity with the intended target of observation. |WATCHES_summary| - -Examples of intended observations include the a follow of a focal -and the regular observation of individuals at the feeding -station. -WATCHES contains one row for each follow, and contains one row per -day, per individual observed at the feeding station. -Examples of ad-hoc observations not associated with any one individual -are observations of pantgrunts or matings recorded during happenstance -encounters. +WATCHES does not contain a row for every day every individual was +observed because SokweDB contains data on individuals collected due to +proximity with the intended target of observation. The |WATCHES.Type| column is used to distinguish and identify the -various important categories: follows, feeding station observations, -etc. +various important categories. +There may only be one row on WATCHES per category, per day, per +individual. +This means, the combination of |WATCHES.Type|, |WATCHES.AnimID| and +|WATCHES.Date| must be unique. + +The |WATCHES.Type| categories are: -The available |WATCHES.Type| values are: +.. _WATCHES_sdb_arec_anchor: + +``sdb_arec`` (A-Record) + Each row represents either a day during which a chimpanzee was + observed at the feeding station or a day when point sample grooming + scans were done at the feeding station. -``sdb_attendance`` (Attendance) - Each row represents a day during which a chimpanzee was observed at - the feeding station. When a given chimpanzee is observed at the feeding station more than once per-day, there are multiple |EVENTS| rows, with an |EVENTS|.\ |EVENTS.Behavior| value of ``sdb_arrival_a``, related to the WATCHES row recording the presence of the individual at the feeding station on the given day. - The |WATCHES.AnimID| column contains the |BIOGRAPHY_DATA|.\ + The |WATCHES.AnimID| column must contain the |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of the individual that appeared at the + feeding station, when there is a record of attendance at the feeding station. + This means when there is a related |ARRIVALS_A| row. + + When there is no related |ARRIVALS_A| row, when there are only + related |EVENTS| rows with an |EVENTS|.\ |EVENTS.Behavior| value of + ``sdb_groom_scan_a`` to record interval scans for grooming, the + expectation is that the individual recorded in the + |WATCHES.AnimID| column, will be ``sdb_unk``. + The system will generate a warning when when the |WATCHES.AnimID| + is not ``sdb_unk`` and there are related |EVENTS| rows with an + |EVENTS|.\ |EVENTS.Behavior| of ``sdb_groom_scan_a``. + + The system expects, but does not require, that individuals recorded + as arriving at the feeding station be recognized. + The system will generate a warning when |WATCHES.AnimID| is the + unknown individual, ``sdb_unk``, and there is a related + |ARRIVALS_A| row. The |WATCHES.CommID| column contains the code for the community the - observers recorded when the attendance was taken; the |COMM_IDS|.\ - |COMM_IDS.CommID| of the community. - This value is not validated against the arriving individual's + observers recorded at the time; the |COMM_IDS|.\ |COMM_IDS.CommID| + of a community. + This value is not validated against an arriving individual's record of community membership as recorded in other database content. + This value is not validated against the community the individuals + involved in grooming are a member of, as recorded in other database + content. + Therefore, care should be taken when using the |WATCHES.CommID| + value recorded with A-Record data. The system will generate a warning when a WATCHES row that represents attendance does not have a record of attendance -- an @@ -81,130 +106,90 @@ The available |WATCHES.Type| values are: ``sdb_arrival_a``, and a |ROLES| row, related to the event, where the |ROLES|.\ |ROLES.Participant| has the same value as the |WATCHES.AnimID| column. + An exception is made for the unknown individual, when the + |WATCHES.AnimID| value is ``sdb_unk``. + In this case no warning is generated. - The system will generate a warning if the ``sdb_male_swelling`` code - is assigned to a female, an individual with a |BIOGRAPHY_DATA|.\ - |BIOGRAPHY_DATA.Sex| value of ``sdb_female``. - - The system will generate a warning if any code other than the - ``sdb_male_swelling`` code is assigned to an individual who is not a - female, an individual with a |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.Sex| - value that is not ``sdb_female``. - - Except for the unknown female individuals, those with a - |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of ``sdb_stranger_female``, - or ``sdb_stranger_female2``, or ``sdb_stranger_female3``, the system - will generate a warning if the ``sdb_adolescent_swelling`` code is - assigned to a female less than ``sdb_min_adolescent_age`` - sdb_min_adolescent_age_units old or more than - ``sdb_max_adolescent_age`` sdb_max_adolescent_age_units old. - - Except for the unknown female individuals, those with a - |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of ``sdb_stranger_female``, - or ``sdb_stranger_female2``, or ``sdb_stranger_female3``, the system - will generate a warning if a code that is not one of - ``sdb_male_swelling``, ``sdb_adolescent_swelling``, - ``sdb_no_swelling``, and ``sdb_missing_swelling`` is assigned to a - female that is at least ``sdb_min_swelling_age`` - sdb_min_swelling_age_units old and less than or equal to - ``sdb_max_swelling_age`` sdb_max_swelling_age_units old. - - For any given individual there may not be more than one record of - daily attendance at the feeding station on any given day. - This means, the combination of |WATCHES.Type|, |WATCHES.AnimID| and - |WATCHES.Date| must be unique. If an individual arrives at and leaves the feeding station multiple times on a single day, the one WATCHES row has - multiple related rows on the |EVENTS| table. - -``sdb_brec`` (B-Record taken) - Each row represents a date on which an individual has B-Record - notes translated into English, but there is no follow on record in - the database. - - The |WATCHES.AnimID| column contains the |BIOGRAPHY_DATA|.\ - |BIOGRAPHY_DATA.AnimID| of the individual. - - The |WATCHES.CommID| column contains the code for the community - recorded along with the B-Record notes; the |COMM_IDS|.\ - |COMM_IDS.CommID| of the community. - This is not necessarily the community the individual is a member - of, although it usually is. - - There may only be one row on WATCHES per day to record an - individual who has translated B-Record notes. - This means, the combination of |WATCHES.Type|, |WATCHES.AnimID| and - |WATCHES.Date| must be unique. - If there were multiple times on a single day that B-Record notes - were taken, the one WATCHES row is related to multiple rows on the - |EVENTS| table. - -``sdb_follow`` (Follow) - Each row represents a date during which observers were assigned - to follow a focal individual. - - A follow may or may not have any related observations, related - |EVENTS| rows. - - The |WATCHES.AnimID| column contains the |BIOGRAPHY_DATA|.\ - |BIOGRAPHY_DATA.AnimID| of the focal of the follow. - - The |WATCHES.CommID| column contains the code for the community - the observers are associated with and intended to follow; the - |COMM_IDS|.\ |COMM_IDS.CommID| of the community. - This is not necessarily the community the focal is a member of, - although it usually is. - - The system will generate a warning when a WATCHES row that - represents a follow does not have a |ARRIVALS| related row that - records the presence of the focal. + multiple related |ARRIVALS_A| rows on the |EVENTS| table. + When multiple individuals were found to be grooming, during + interval scans for grooming conducted at the feeding station on any + given day, the one WATCHES row has multiple related + rows on the |EVENTS| table with an |EVENTS|.\ |EVENTS.Behavior| + value of ``sdb_groom_scan_a``. + +.. _WATCHES_sdb_brec_anchor: + +``sdb_brec`` (B-Record ) + Each row represents a date/\ |WATCHES.AnimID| pairing on which + information normally collected during B-Record follows was + recorded. + There is not necessarily a Tiki sheet, or a follow, that + corresponds to the date/individual pairing. + + If there are related rows on |ARRIVALS| it is safe to assume a + follow was conducted on a focal individual on that day. + But, it is also possible for a follow to have no related + observations, related |EVENTS| rows.\ [#f1]_ + For follows, the |WATCHES.AnimID| is the |BIOGRAPHY_DATA|.\ + |BIOGRAPHY_DATA.AnimID| of the focal.\ [#f2]_ + The |WATCHES.AnimID| column also contains the |BIOGRAPHY_DATA|.\ + |BIOGRAPHY_DATA.AnimID| of the individual purported to be the focal + of a |BRECORD_NOTES| translation, but there is no record of such a + follow. + + If no follow was conducted and there is no related |BRECORD_NOTES| + row, then the |WATCHES.AnimID| is expected to be that of the unknown + individual, ``sdb_unk``. + This means that the system will generate a warning when there is + no |ARRIVALS| or |BRECORD_NOTES| row related to the |WATCHES| row, + and the WATCHES.\ |WATCHES.AnimID| value is *not* ``sdb_unk``. + + Like any other |WATCHES.AnimID| value, there is at most one row per + day involving the unknown individual. + + The focal of a follow is expected to have been observed during the + follow. + This means the system will generate a warning when a WATCHES row of + this type does not have a |ARRIVALS| related row that records the + presence of the focal. This means that warning is generated when a WATCHES row with a - |WATCHES.Type| value of ``sdb_follow`` does not have a related + |WATCHES.Type| value of ``sdb_brec`` does not have a related |EVENTS| row where the |EVENTS.Behavior| code is ``sdb_arrival``, and a |ROLES| row, related to the event, where the |ROLES|.\ |ROLES.Participant| has the same value as the follow's |WATCHES.AnimID| column. + An exception is made in the case of the unknown individual, no + warning is generated when the |WATCHES.AnimID| is the unknown + individual ,``sdb_unk``. + + When no follow occurred, the |WATCHES.AnimID| value is expected to + be, in some sense, meaningless. + This means the system will generate a warning when a WATCHES row of + this type does not have a |ARRIVALS| related row and the + |WATCHES.AnimID| value is not that of the unknown individual, + ``sdb_unk``. + These sorts of watches rows may appear when there are ad-hoc + observations of behaviors like pantgrunts, matings, aggressions, + and so forth. - A follow must have at least one related |FOLLOW_OBSERVERS| rows, to - record the observers scheduled to conduct the follow. + When there are related |ARRIVALS| rows there must be at least one + related |FOLLOW_OBSERVERS| row, to record the observers scheduled + to conduct the follow. |transaction commit| - There may only be one row on WATCHES to record a follow of any - given individual on any given day. - This means, the combination of |WATCHES.Type|, |WATCHES.AnimID| and - |WATCHES.Date| must be unique. - -``sdb_ag_scan`` (Attendance Groom scan) - - Each row records a date on which groomings were scanned for on - periodic intervals. - There is one WATCHES row of this type per date. - - The expectation is that the individual associated *with* *the* - *scan* (not the grooming), the individual recorded in the - |WATCHES.AnimID| column, will always be ``sdb_unk``. - The system will generate a warning when the associated individual - is not ``sdb_unk``, when |WATCHES.AnimID| is not ``sdb_unk``. - - The |WATCHES.AnimID| column contains little information that is - useful, because feeding station groom scans were not associated - with any type of observation that specifically targeted a - particular individual. - The |WATCHES.CommID| column contains the code for the community - recorded at along with the pantgrunt; the |COMM_IDS|.\ + recorded when the observation was taken; the |COMM_IDS|.\ |COMM_IDS.CommID| of the community. - This is not necessarily the community the individual(s) involved in - the grooming is(are) a member of, although it usually is. + This is not necessarily the community the individual is a member + of, although it usually is. - There may only be one row on WATCHES per day to record interval - scans for grooming at the feeding station. - This means, the combination of |WATCHES.Type| and |WATCHES.Date| - must be unique. - When multiple individuals were found to be grooming, during - interval scans for grooming conducted at the feeding station on any - given day, the one WATCHES row has multiple related rows on the - |EVENTS| table. + If there were multiple times on a single day that B-Record notes + were taken, the one WATCHES row is related to multiple + |BRECORD_NOTES| rows through the |EVENTS| table. + +.. _WATCHES_sdb_location_anchor: ``sdb_location`` (Location) Each row represents a date on which an individual was spatially @@ -219,120 +204,28 @@ The available |WATCHES.Type| values are: This is not necessarily the community the located individual is a member of, although it usually is. - There may only be one row on WATCHES per day to record an - individual who was spatially located. - This means, the combination of |WATCHES.Type|, |WATCHES.AnimID| and - |WATCHES.Date| must be unique. If an individual's location is recorded multiple times on a single day, the one WATCHES row is related to multiple rows on the |EVENTS| table. -``sdb_mating`` (Mating) - - Each row records a date on which individuals were observed to - mate, when there is no follow assocated with the mating. - There is one WATCHES row of this type per date, per some usually - non-meaningful |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| -- often - ``sdb_unk``. - - The |WATCHES.AnimID| column contains little information that is - useful. - The |WATCHES.AnimID| column contains the |BIOGRAPHY_DATA|.\ - |BIOGRAPHY_DATA.AnimID| of the individual that was purportedly the - focal of a follow, but no such follow of the individual exists. - When there is no focal on record, for whatever reason, the special - |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| value of ``sdb_unk``, - representing an unknown individual -- or, in this case, "no - individual", is expected to be the |WATCHES.AnimID| value. +.. _WATCHES_sdb_other_watch_anchor: - The |WATCHES.CommID| column contains the code for the community - recorded at along with the mating; the |COMM_IDS|.\ - |COMM_IDS.CommID| of the community. - This is not necessarily the community the mating individuals - are a member of, although it usually is. - - There may only be one row on WATCHES per day, per individual - recorded along with the mating data. - This means, the combination of |WATCHES.Type|, |WATCHES.AnimID| and - |WATCHES.Date| must be unique. - When multiple matings are recorded in a follow, or are recorded - on some day when there is no follow, the one WATCHES - row has multiple related rows on the |EVENTS| table. - -``sdb_pantgrunt`` (Pantgrunt) - - Each row records a date on which an individual was observed to - pantgrunt, when there is no follow assocated with the pantgrunt. - There is one WATCHES row of this type per date, per some usually - non-meaningful |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| -- often - ``sdb_unk``. +``sdb_other_watch`` (Other) - The |WATCHES.AnimID| column contains little information that is - useful. - The |WATCHES.AnimID| column contains the |BIOGRAPHY_DATA|.\ - |BIOGRAPHY_DATA.AnimID| of the individual that was purportedly the - focal of a follow, but no such follow of the individual exists. - When there is no focal on record, for whatever reason, the special - |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| value of ``sdb_unk``, - representing an unknown individual -- or, in this case, "no - individual", is expected to be the |WATCHES.AnimID| value. + This |WATCHES.Type| value is almost used exactly as the + :ref:`sdb_brec ` is used. + It exists so that in the future (at the time of this writing) other + behaviors, observed and recorded using other protocols, can be + easily incorporated into SokweDB. - The |WATCHES.CommID| column contains the code for the community - recorded at along with the pantgrunt; the |COMM_IDS|.\ - |COMM_IDS.CommID| of the community. - This is not necessarily the community the pantgrunting individual(s) - is(are) a member of, although it usually is. - - There may only be one row on WATCHES per day, per individual - recorded along with the pantgrunt data. - This means, the combination of |WATCHES.Type|, |WATCHES.AnimID| and - |WATCHES.Date| must be unique. - When multiple pantgrunts are recorded in a follow, or are recorded - on some day when there is no follow, the one WATCHES - row has multiple related rows on the |EVENTS| table. - -WATCHES rows of the B-Record type, |WATCHES.Type| = ``sdb_brec``, -may only exist when there is not a follow-type row, |WATCHES.Type| = -``sdb_follow``, for the individual on the given date. -This means that if an individual has translated B-Records on a day -they are followed, there cannot be a row on WATCHES for that -individual for that day with a |WATCHES.Type| of ``sdb_brec``, and -vice-versa. - -WATCHES rows of the location type, |WATCHES.Type| = ``sdb_location``, -may only exist when there is not a follow-type row, |WATCHES.Type| = -``sdb_follow``, for the individual on the given date. -This means that if an individual is spatially located on a day they -are followed there cannot be a row on WATCHES for that individual -for that day with a |WATCHES.Type| of ``sdb_location``, and -vice-versa. - -WATCHES rows of the mating type, |WATCHES.Type| = ``sdb_mating``, -may only exist when there is not a follow-type row, |WATCHES.Type| = -``sdb_follow``, for the individual on the given date. -This means that if an individual is a participates in a mating event -on a day they are followed there cannot be a row on WATCHES for that -individual for that day with a |WATCHES.Type| of ``sdb_mating``, and -vice-versa. - -WATCHES rows of the pantgrunt type, |WATCHES.Type| = ``sdb_pantgrunt``, -may only exist when there is not a follow-type row, |WATCHES.Type| = -``sdb_follow``, for the individual on the given date. -This means that if an individual pantrgunts on a day they -are followed there cannot be a row on WATCHES for that individual -for that day with a |WATCHES.Type| of ``sdb_pantgrunt``, and -vice-versa. - -The intention is that follows, WATCHES rows where the |WATCHES.Type| = -``sdb_follow``, be used where data on a focal is recorded. -The system will generate a warning when there is a follow for which -there are only B-Record related events. -The system will generate a warning when there is a follow for which -there are only location related events. -The system will generate a warning when there is a follow for which -there are only mating related events. -The system will generate a warning when there is a follow for which -there are only pantgrunt related events. + The one difference between a |WATCHES.Type| of ``sdb_other_watch`` + and a |WATCHES.Type| of ``sdb_brec``, is that the + ``sdb_other_watch`` |WATCHES.Type| is not expected to have related + |ARRIVALS| rows. + Unlike WATCHES rows with a |WATCHES.Type| of ``sdb_brec``, which + generate warnings when there are *not* related |ARRIVALS| rows, + WATCHES rows with a |WATCHES.Type| value of ``sdb_other_watch`` + generate warnings when there *are* related |ARRIVALS| rows. The |WATCHES.Date| must be during a period when the watched individual was under study. @@ -417,19 +310,13 @@ the data collection protocol used. One of the following values: -``sdb_attendance`` (Attendance) +:ref:`sdb_arec ` (A-Record) -``sdb_brec`` (B-Record) +:ref:`sdb_brec ` (B-Record) -``sdb_follow`` (Follow) +:ref:`sdb_location ` (Location) -``sdb_ag_scan`` (Attendance Groom Scan) - -``sdb_location`` (Location) - Only used when there is no follow to use. - -``sdb_pantgrunt`` (Pantgrunt) - Only used when there is no follow to use. +:ref:`sdb_other_watch ` (Other) See :ref:`overview of the WATCHES table `, above, for more information. @@ -447,3 +334,19 @@ Notes Free form textual notes on the watch period. |WATCHES.Notes_summary| |notonlyspaces| |notnull| + + +.. rubric:: Footnotes + +.. [#f1] + As might happen if a Tiki sheet was produced, but for some reason + the observers never reached the field. + +.. [#f2] + There is no way for the system to enforce the requirement that the + focal of a follow be the WATCHES.|WATCHES.AnimID| value. + This is because there is no particular requirement, available + within the data, a follow must meet in order to be called a follow. + That said, as noted, the system generates a warning when there is + B-Record type data and there is no recorded arrival for the + individual recorded in the |WATCHES.AnimID| column. diff --git a/include/global_constants.m4 b/include/global_constants.m4 index 7e24788..eda7bc3 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -201,13 +201,10 @@ dnl WATCHES dnl dnl The WATCHES.Type values -define(`sdb_attendance', `A') +define(`sdb_arec', `A') define(`sdb_brec', `B') -define(`sdb_follow', `F') -define(`sdb_ag_scan', `G') define(`sdb_location', `L') -define(`sdb_mating', `M') -define(`sdb_pantgrunt', `P') +define(`sdb_other_watch', `O') divert(`0')dnl Output with m4 again ]}])dnl End of ifdef over the whole file. -- 2.34.1